<?php
	function get_category_hierarchy($languageID, $parentID = 0 , $urlPrefix = "")
	{
		global $connection; 
		
		$query = "SELECT c.id, cd.name, cd.url, cd.alt_title FROM categories c, category_details cd 
	 												WHERE c.parent_id = ? AND cd.category_id = c.id AND cd.language_id = ? 
	 													AND cd.name != '' 
	 												ORDER BY cd.name";
	 	$categories = $connection->fetch_result($query, array($parentID, $languageID));
		
		foreach($categories as $key => $category)
		{
			$categoryURL = $categories[$key]["slug"] = $category["url"];
			
			if(isset($_GET["super_category"]) && $categoryURL == $_GET["super_category"])
			{
				$categories[$key]["selected"] = true;
			}
			elseif(isset($_GET["category"]) && $categoryURL == $_GET["category"])
			{
				$categories[$key]["selected"] = true;
			}
			elseif(isset($_GET["sub_category"]) && $categoryURL == $_GET["sub_category"])
			{
				$categories[$key]["selected"] = true;
			}
			
			$categories[$key]["url"] = $urlPrefix . $categoryURL. "/";
			
			$childPrefix = $urlPrefix . $categoryURL . "/";
			
			$categories[$key]["num_rows"] = $connection->num_rows("SELECT p.id FROM products p 
										WHERE p.id IN (SELECT pc.product_id AS id FROM products_categories pc WHERE pc.category_id = ?)  
														AND p.visible > 0 GROUP BY p.id", array($category["id"]));
			
			
										
			$children = get_category_hierarchy($languageID, $category["id"], $childPrefix);
			
			if(count($children) > 0)
			{
				$categories[$key]["children"] = $children;
				foreach($children as $child)
				{
					$categories[$key]["num_rows"] += $child['num_rows'];
				}
			}
			
			
		}
		if(count($categories) > 0)
		{
			return $categories;
		}
	}
	
	function get_category($url, $languageID)
	{
		global $connection;
		
		$category = $connection->fetch_one("SELECT cd.*, cd.category_id AS id, c.parent_id FROM category_details cd, categories c
										WHERE cd.url = ? AND c.id = cd.category_id AND  cd.language_id = ?", array($url, $languageID));
		if(isset($category["id"]))
		{
			$category["properties"] = get_category_properties($category["id"]);
			return $category;
		}
	}
	
	function get_category_by_id($categoryID, $languageID)
	{
		global $connection;
		
		$category = $connection->fetch_one("SELECT cd.*, cd.category_id AS id FROM category_details cd 
										WHERE cd.category_id = ? AND  cd.language_id = ?", array($categoryID, $languageID));
		return $category;
	}
	
	function get_promoted_products($categoryID)
	{
		global $connection;
		
		$items = $connection->fetch_result("SELECT product_id FROM categories_promoted_products WHERE category_id = ?", array($categoryID));
		$products = false;
		
		if($items)
		{
			$product_ids = array();
			foreach($items as $item)
			{
				$product_ids[$item["product_id"]] = $item["product_id"];
			}
			
			$products = getProductByField($product_ids, "id");
		}
		
		return $products;
	}
	
	
	function get_category_properties($categoryID, $productID = null, $stockID = null)
	{
		global $connection;
		
		$languageID = $_SESSION["language"]["id"];
		
		$properties = array();
		
		$query = "SELECT s.id, s.properties FROM products p
				    LEFT JOIN stocks s ON p.id = s.product_id
				  WHERE ";
		
		
		
		if($stockID != null)
		{
			$params[] = $stockID;
			$query .= " s.id = ?";
		}
		elseif($productID != null)
		{
			$params[] = $productID;
			$query .= " p.id = ?";
		}
		elseif($categoryID)
		{
			$params[] = $categoryID;
			
			$query .= " p.id IN (SELECT pc.product_id AS id FROM products_categories pc WHERE  pc.category_id = ?)";
		}
		$query .= " GROUP BY s.id";
		
		$stocks = $connection->fetch_result($query, $params);
		foreach($stocks as $stock)
		{
			if($stock["properties"] != "")
			{
				$stockProperties = json_decode($stock["properties"], true);
				foreach ($stockProperties as $propertyID => $propertyValueID) 
				{
					if(!isset($properties[$propertyID]))
					{
						$properties[$propertyID] = $connection->fetch_one("SELECT p.*, pd.name FROM properties p 
																				LEFT JOIN properties_details pd ON p.id = pd.property_id
																			WHERE p.id = ? AND pd.language_id = ?",array($propertyID, $languageID));
					}
					
					if(isset($properties[$propertyID]["id"]))
					{
						if(!isset($properties[$propertyID]["values"][$propertyValueID]))
						{
							$properties[$propertyID]["values"][$propertyValueID] = $connection->fetch_one("SELECT pv.id,pv.value, pvd.name, pvd.url, pf.name AS picture, pf.thumb_name AS thumb_name, {$stock["id"]} AS stock_id FROM properties_values pv 
					      	LEFT JOIN properties_values_details pvd ON pvd.property_value_id = pv.id
					      	LEFT JOIN picture_files pf ON pf.id IN( SELECT sp.picture_id FROM stock_pictures sp WHERE sp.stock_id = ?) 
					    	WHERE pvd.language_id = ? AND pv.property_id = ? AND pv.id = ?", array($stock["id"], $languageID, $propertyID, $propertyValueID));
						}
					}
				}
			}
		}
		
		return $properties;
	}
	
	function get_property_values($propertyID, $languageID)
	{
		global $connection;
		
		$values = $connection->fetch_result("SELECT pv.id,pv.value, pvd.name, pvd.url FROM properties_values pv
					      	LEFT JOIN properties_values_details pvd ON pvd.property_value_id = pv.id
					    WHERE pvd.language_id = ? AND pv.property_id = ?", array($languageID, $propertyID));
		return $values;
	}
	
	function get_property_by_url($propertyURL, $languageID)
	{
		global $connection;
		$property = $connection->fetch_one("SELECT pv.property_id, pvd.property_value_id, pvd.url FROM properties_values_details pvd
						    LEFT JOIN properties_values pv ON pvd.property_value_id = pv.id
						     WHERE url = ? AND language_id = ?", array($propertyURL, $languageID));
		return $property;
	}
	
	function makeCategoryURL($categoryID, &$url)
	{
		global $connection;
		
		$languageID = $_SESSION["language"]["id"];
		
		$category = $connection->fetch_one("SELECT cd.url, c.parent_id FROM category_details cd, categories c 
												WHERE cd.category_id = ? AND cd.language_id = ? 
												AND c.id = cd.category_id", array($categoryID, $languageID));
		if($category)
		{
			if($category["parent_id"] > 0)
			{
				makeCategoryURL($category["parent_id"], $url);
			}
			
			$url.= $category["url"]."/";
		}
	}
?>